\c pincaicai postgres;

-- =========================
-- Stored functions
-- =========================

-- 1. Function to calculate order total amount
CREATE OR REPLACE FUNCTION calculate_order_total(order_id_param INT)
RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC := 0;
BEGIN
    SELECT COALESCE(SUM(quantity * price), 0)
    INTO total
    FROM order_items
    WHERE order_id = order_id_param;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- 2. Function to update user balance
CREATE OR REPLACE FUNCTION update_user_balance(user_id_param INT, amount NUMERIC)
RETURNS BOOLEAN AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- Get current balance
    SELECT balance INTO current_balance
    FROM users
    WHERE user_id = user_id_param;
    
    -- Check if balance is sufficient
    IF current_balance + amount < 0 THEN
        RETURN FALSE;
    END IF;
    
    -- Update balance
    UPDATE users
    SET balance = balance + amount
    WHERE user_id = user_id_param;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- 3. Function to check stock
CREATE OR REPLACE FUNCTION check_stock(seafood_id_param INT, required_quantity INT)
RETURNS BOOLEAN AS $$
DECLARE
    available_stock INT;
BEGIN
    SELECT stock INTO available_stock
    FROM seafood
    WHERE product_id = seafood_id_param;
    
    RETURN available_stock >= required_quantity;
END;
$$ LANGUAGE plpgsql;

-- 4. Function to update stock
CREATE OR REPLACE FUNCTION update_stock(seafood_id_param INT, quantity_change INT)
RETURNS BOOLEAN AS $$
DECLARE
    current_stock INT;
BEGIN
    -- Get current stock
    SELECT stock INTO current_stock
    FROM seafood
    WHERE product_id = seafood_id_param;
    
    -- Check if stock is sufficient
    IF current_stock + quantity_change < 0 THEN
        RETURN FALSE;
    END IF;
    
    -- Update stock
    UPDATE seafood
    SET stock = stock + quantity_change
    WHERE product_id = seafood_id_param;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- 5. Function to get user default address
CREATE OR REPLACE FUNCTION get_user_default_address(user_id_param INT)
RETURNS INT AS $$
DECLARE
    address_id_result INT;
BEGIN
    SELECT address_id INTO address_id_result
    FROM addresses
    WHERE user_id = user_id_param AND is_default = TRUE
    LIMIT 1;
    
    RETURN address_id_result;
END;
$$ LANGUAGE plpgsql;

-- =========================
-- Trigger functions
-- =========================

-- 1. Automatically calculate total amount when order is created
CREATE OR REPLACE FUNCTION trigger_calculate_order_total()
RETURNS TRIGGER AS $$
BEGIN
    NEW.total_price := calculate_order_total(NEW.order_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. Update order total amount when order items are inserted/updated
CREATE OR REPLACE FUNCTION trigger_update_order_total()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        UPDATE orders
        SET total_price = calculate_order_total(NEW.order_id)
        WHERE order_id = NEW.order_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE orders
        SET total_price = calculate_order_total(OLD.order_id)
        WHERE order_id = OLD.order_id;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- 3. Deduct stock and balance when order is confirmed
CREATE OR REPLACE FUNCTION trigger_process_order()
RETURNS TRIGGER AS $$
DECLARE
    item RECORD;
    stock_updated BOOLEAN;
    balance_updated BOOLEAN;
BEGIN
    -- Only process when status changes to confirmed
    IF NEW.status = 'confirmed' AND OLD.status != 'confirmed' THEN
        
        -- Check stock for all order items
        FOR item IN 
            SELECT product_id, quantity 
            FROM order_items 
            WHERE order_id = NEW.order_id
        LOOP
            IF NOT check_stock(item.product_id, item.quantity) THEN
                RAISE EXCEPTION 'Insufficient stock for product_id: %', item.product_id;
            END IF;
        END LOOP;
        
        -- Check user balance
        IF NOT update_user_balance(NEW.user_id, -NEW.total_price) THEN
            RAISE EXCEPTION 'Insufficient balance for user_id: %', NEW.user_id;
        END IF;
        
        -- Deduct stock
        FOR item IN 
            SELECT product_id, quantity 
            FROM order_items 
            WHERE order_id = NEW.order_id
        LOOP
            PERFORM update_stock(item.product_id, -item.quantity);
        END LOOP;
        
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 4. Check stock when cart item is added
CREATE OR REPLACE FUNCTION trigger_check_cart_stock()
RETURNS TRIGGER AS $$
DECLARE
    current_stock INT;
BEGIN
    -- Check stock based on product category
    IF NEW.category = 'seafood' THEN
        SELECT stock INTO current_stock FROM seafood WHERE product_id = NEW.product_id;
    ELSIF NEW.category = 'vegetables' THEN
        SELECT stock INTO current_stock FROM vegetables WHERE product_id = NEW.product_id;
    ELSIF NEW.category = 'meats' THEN
        SELECT stock INTO current_stock FROM meats WHERE product_id = NEW.product_id;
    ELSIF NEW.category = 'fruits' THEN
        SELECT stock INTO current_stock FROM fruits WHERE product_id = NEW.product_id;
    ELSE
        RAISE EXCEPTION 'Invalid product category: %', NEW.category;
    END IF;
    
    -- Check if stock is sufficient
    IF current_stock IS NULL THEN
        RAISE EXCEPTION 'Product not found: % in category %', NEW.product_id, NEW.category;
    END IF;
    
    IF current_stock < NEW.quantity THEN
        RAISE EXCEPTION 'Insufficient stock for product_id: % in category: %. Available: %, Required: %', 
            NEW.product_id, NEW.category, current_stock, NEW.quantity;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 5. Ensure only one default address
CREATE OR REPLACE FUNCTION trigger_ensure_single_default_address()
RETURNS TRIGGER AS $$
BEGIN
    -- If setting new address as default, cancel other default addresses
    IF NEW.is_default = TRUE THEN
        UPDATE addresses
        SET is_default = FALSE
        WHERE user_id = NEW.user_id 
        AND address_id != NEW.address_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 6. Automatically set creation time
CREATE OR REPLACE FUNCTION trigger_set_created_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- =========================
-- Create triggers
-- =========================

-- Order table triggers
CREATE TRIGGER trg_calculate_order_total
    BEFORE INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION trigger_calculate_order_total();

CREATE TRIGGER trg_process_order
    AFTER UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION trigger_process_order();

-- Order items table triggers
CREATE TRIGGER trg_update_order_total_insert
    AFTER INSERT ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION trigger_update_order_total();

CREATE TRIGGER trg_update_order_total_update
    AFTER UPDATE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION trigger_update_order_total();

CREATE TRIGGER trg_update_order_total_delete
    AFTER DELETE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION trigger_update_order_total();

-- Shopping cart table triggers
CREATE TRIGGER trg_check_cart_stock
    BEFORE INSERT OR UPDATE ON shopping_cart
    FOR EACH ROW
    EXECUTE FUNCTION trigger_check_cart_stock();

-- Address table triggers
CREATE TRIGGER trg_ensure_single_default_address
    BEFORE INSERT OR UPDATE ON addresses
    FOR EACH ROW
    EXECUTE FUNCTION trigger_ensure_single_default_address();

-- User table triggers
CREATE TRIGGER trg_set_user_created_at
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_created_at();

-- Address table triggers
CREATE TRIGGER trg_set_address_created_at
    BEFORE INSERT ON addresses
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_created_at();

-- Order table triggers
CREATE TRIGGER trg_set_order_created_at
    BEFORE INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_created_at();

-- Review table triggers
CREATE TRIGGER trg_set_review_created_at
    BEFORE INSERT ON reviews
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_created_at();

-- =========================
-- Views
-- =========================

-- 1. User order statistics view
CREATE OR REPLACE VIEW user_order_stats AS
SELECT 
    u.user_id,
    u.username,
    COUNT(o.order_id) as total_orders,
    COALESCE(SUM(o.total_price), 0) as total_spent,
    COALESCE(AVG(o.total_price), 0) as avg_order_amount,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username;

-- 2. Seafood sales statistics view
CREATE OR REPLACE VIEW seafood_sales_stats AS
SELECT 
    s.product_id,
    s.name,
    s.price,
    s.stock,
    COUNT(oi.item_id) as times_ordered,
    COALESCE(SUM(oi.quantity), 0) as total_quantity_sold,
    COALESCE(SUM(oi.quantity * oi.price), 0) as total_revenue,
    COALESCE(AVG(r.rating), 0) as avg_rating,
    COUNT(r.review_id) as review_count
FROM seafood s
LEFT JOIN order_items oi ON s.product_id = oi.product_id AND oi.category = 'seafood'
LEFT JOIN reviews r ON oi.order_id = r.order_id
GROUP BY s.product_id, s.name, s.price, s.stock;

-- 3. Order details view
CREATE OR REPLACE VIEW order_details AS
SELECT 
    o.order_id,
    o.user_id,
    u.username,
    o.status,
    o.total_price,
    o.created_at,
    a.address,
    a.city,
    a.province,
    a.phone as delivery_phone,
    STRING_AGG(
        s.name || ' x' || oi.quantity || ' @$' || oi.price,
        ', '
        ORDER BY oi.item_id
    ) as items
FROM orders o
JOIN users u ON o.user_id = u.user_id
LEFT JOIN addresses a ON o.address_id = a.address_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN seafood s ON oi.product_id = s.product_id AND oi.category = 'seafood'
GROUP BY o.order_id, o.user_id, u.username, o.status, o.total_price, o.created_at, a.address, a.city, a.province, a.phone;
